package com.whu;

import javafx.beans.binding.ObjectExpression;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DBTest {
    private String url;
    private String user;
    private String password;

    public DBTest() {
        try {
            url = "jdbc:oracle:thin:@localhost:1521:orcl";
            user = "scott";
            password = "123456";
            Class.forName("oracle.jdbc.driver.OracleDriver"); //加载mysq驱动
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载错误");
            e.printStackTrace();//打印出错详细信息
        }
    }
    public String getUser(){
        return user;
    }
    public Connection getConn() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            System.out.println("数据库连接错误");
            e.printStackTrace();
        }
        return conn;
    }

    //关闭数据库
    public void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            System.out.println("数据库关闭错误");
            e.printStackTrace();
        }
    }

    //select
    public static Table query(String sql) throws Exception {
        Table table = new Table();
        DBTest dbTest = new DBTest();
        Connection conn = dbTest.getConn();
        Statement stmt = null;
        ResultSet rs = null;
        String[][] newMatrix;
        String[] elements;
        try {
            System.out.println("query函数中的sql为："+sql);
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
            rs.last();

            //获取矩阵的行数和列数
            int rowCount = rs.getRow();
            rs.beforeFirst();//还要用rs，所以把指针移到最前
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            String[] names = new String[columnCount];
            for (int i = 0; i < columnCount; i++){
                names[i] = rsmd.getColumnName(i+1);
            }
            table.setElements(names);
            elements = table.getElements();

            table.setRow(rowCount);
            table.setColumn(columnCount);
            //通过哈希表来获取select的任意数据
            HashMap<String, Object> map = new HashMap<>();
            newMatrix = new String[rowCount][];
            for (int i = 0; rs.next(); i++) {
                newMatrix[i] = new String[columnCount];
                //初始化哈希表
                for (int j = 0; j < columnCount; j++)
                    map.put(elements[j], "");

                //把某一行的所有元素对应的数据存入哈希表
                for (int j = 0; j < columnCount; j++) {
                    for (int k = 0; k < elements.length; k++) {
                        if (rsmd.getColumnTypeName(j+1).toUpperCase().equals("NUMBER")) {
                            map.put(elements[j], rs.getInt(elements[j]));
                        } else {
                            map.put(elements[j], rs.getObject(elements[j]));
                        }
                    }
                }
                //把该哈希表的数据传给table
                for (int j = 0; j < columnCount; j++)
                    newMatrix[i][j] = String.valueOf(map.get(elements[j]));
            }
            table.setMatrix(newMatrix);
        } catch (SQLException e) {
            System.out.println("query函数数据操作错误");
            e.printStackTrace();
        }

        dbTest.close(conn, stmt, rs);

        return table;
    }

    //create,drop,update,insert,delete,create procedure
    public static void executeTable(String sql) {
        DBTest dbTest = new DBTest();
        Connection conn = dbTest.getConn();
        Statement stmt = null;
        ResultSet rs = null;
        String[] tableList = Table.getTableList();
        System.out.println("execute中sql为："+sql);
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("数据操作错误");
            e.printStackTrace();
        }
        if(tableList!=null){
            for(int i = 0;i<tableList.length;i++){
                if(sql.toUpperCase().contains(tableList[i]))
                    Table.setTableName(tableList[i]);
            }
        }
        String[] array = sql.split(" ");
        if(array[0].toLowerCase().equals("drop") && array[1].toLowerCase().equals("table")){
            Table.setTableName(Table.getTableList()[0]);
        }
        dbTest.close(conn, stmt, rs);
    }

    //当点击submit按钮的时候，对不同sql语句的区别操作
    public static boolean funcWhenSubmit(String sql){
        String[] array = sql.split(" ");
        if(array[0].toLowerCase().equals("select")){
            //返回false说明该函数没有执行executeTable(sql)，sql以select开头
            return false;
        }
        executeTable(sql);
        Table.setTableList(getTables());
        if(null==Table.getTableName()){
            Table.setTableName(Table.getTableList()[0]);
        }
        //返回true说明执行了executeTable(sql)，sql以其他开头
        return true;
    }
    //call procedure
    public static void callProcedure(String sql) {
        DBTest dbTest = new DBTest();
        Connection conn = dbTest.getConn();
        try {
            CallableStatement cs = conn.prepareCall(sql);
            cs.execute();
            ResultSet rs = cs.getResultSet();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //获取该数据库所有表名
    public static String[] getTables(){
        DBTest dbTest = new DBTest();
        Connection conn = dbTest.getConn();
        String[] types = {"TABLE"};
        ArrayList arrayList = new ArrayList();
        try {
            DatabaseMetaData databaseMetaData = conn.getMetaData();
            ResultSet resultSet = databaseMetaData.getTables("conn_scott",null,"%",types);
            while(resultSet.next()){
                String tableScheme = resultSet.getString("TABLE_SCHEM");
                if(dbTest.getUser().equalsIgnoreCase(tableScheme)){
                    String tableName = resultSet.getString("TABLE_NAME");
                    arrayList.add(tableName);
                }
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String[] result = new String[arrayList.size()];
        for(int i = 0;i<arrayList.size();i++){
            result[i] = (String) arrayList.get(i);
        }
        Table.setTableList(result);
        return result;
    }
}
